
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 06/09/2011 17:03:12
-- Generated from EDMX file: D:\@yuxinyang\v4\YiDeSoft\BigTrunk\EzDesk-Dev\Services\Core\EzDesk.Core.Data\EzDeskDatabase.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [EzDeskDatabase];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_GroupDept]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[DeptSet] DROP CONSTRAINT [FK_GroupDept];
GO
IF OBJECT_ID(N'[dbo].[FK_PosotionUserProfile]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserProfileSet] DROP CONSTRAINT [FK_PosotionUserProfile];
GO
IF OBJECT_ID(N'[dbo].[FK_DeptUserProfile]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserProfileSet] DROP CONSTRAINT [FK_DeptUserProfile];
GO
IF OBJECT_ID(N'[dbo].[FK_UserHeadImageUserProfile]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserProfileSet] DROP CONSTRAINT [FK_UserHeadImageUserProfile];
GO
IF OBJECT_ID(N'[dbo].[FK_GroupPermissionGroup]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Permission_GroupSet] DROP CONSTRAINT [FK_GroupPermissionGroup];
GO
IF OBJECT_ID(N'[dbo].[FK_PermissionPermissionGroup]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Permission_GroupSet] DROP CONSTRAINT [FK_PermissionPermissionGroup];
GO
IF OBJECT_ID(N'[dbo].[FK_AppFunctionCategoryAppFunction]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AppFunctionSet] DROP CONSTRAINT [FK_AppFunctionCategoryAppFunction];
GO
IF OBJECT_ID(N'[dbo].[FK_AppFunctionTypeAppFunction]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AppFunctionSet] DROP CONSTRAINT [FK_AppFunctionTypeAppFunction];
GO
IF OBJECT_ID(N'[dbo].[FK_UserProfileAppFunctionShortcut]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AppFunctionShortcutSet] DROP CONSTRAINT [FK_UserProfileAppFunctionShortcut];
GO
IF OBJECT_ID(N'[dbo].[FK_FeedFeed_Record]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Feed_RecordSet] DROP CONSTRAINT [FK_FeedFeed_Record];
GO
IF OBJECT_ID(N'[dbo].[FK_FeedFeed_Recipient]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Feed_RecipientSet] DROP CONSTRAINT [FK_FeedFeed_Recipient];
GO
IF OBJECT_ID(N'[dbo].[FK_FeedTemplateFeed]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[FeedSet] DROP CONSTRAINT [FK_FeedTemplateFeed];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FeedTemplateSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[FeedTemplateSet];
GO
IF OBJECT_ID(N'[dbo].[DeptSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DeptSet];
GO
IF OBJECT_ID(N'[dbo].[GroupSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[GroupSet];
GO
IF OBJECT_ID(N'[dbo].[PositionSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PositionSet];
GO
IF OBJECT_ID(N'[dbo].[UserProfileSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserProfileSet];
GO
IF OBJECT_ID(N'[dbo].[UserHeadImageSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserHeadImageSet];
GO
IF OBJECT_ID(N'[dbo].[PermissionSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PermissionSet];
GO
IF OBJECT_ID(N'[dbo].[Permission_GroupSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Permission_GroupSet];
GO
IF OBJECT_ID(N'[dbo].[OrganizationSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[OrganizationSet];
GO
IF OBJECT_ID(N'[dbo].[AppFunctionSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AppFunctionSet];
GO
IF OBJECT_ID(N'[dbo].[AppFunctionTypeSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AppFunctionTypeSet];
GO
IF OBJECT_ID(N'[dbo].[AppFunctionShortcutSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AppFunctionShortcutSet];
GO
IF OBJECT_ID(N'[dbo].[FeedSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[FeedSet];
GO
IF OBJECT_ID(N'[dbo].[Feed_RecipientSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Feed_RecipientSet];
GO
IF OBJECT_ID(N'[dbo].[Feed_RecordSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Feed_RecordSet];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'FeedTemplateSet'
CREATE TABLE [dbo].[FeedTemplateSet] (
    [Id] uniqueidentifier  NOT NULL,
    [TemplateName] nvarchar(max)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Body] nvarchar(max)  NOT NULL,
    [DefaultTypeValue] int  NOT NULL,
    [TemplateIconName] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'DeptSet'
CREATE TABLE [dbo].[DeptSet] (
    [Id] uniqueidentifier  NOT NULL,
    [DeptName] nvarchar(max)  NOT NULL,
    [GroupId] uniqueidentifier  NULL
);
GO

-- Creating table 'GroupSet'
CREATE TABLE [dbo].[GroupSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Memberstring] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [Type] int  NOT NULL
);
GO

-- Creating table 'PositionSet'
CREATE TABLE [dbo].[PositionSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [Priority] int  NOT NULL
);
GO

-- Creating table 'UserProfileSet'
CREATE TABLE [dbo].[UserProfileSet] (
    [Id] uniqueidentifier  NOT NULL,
    [UserCode] nvarchar(max)  NOT NULL,
    [Name] nvarchar(max)  NULL,
    [DeptId] uniqueidentifier  NULL,
    [PositionId] uniqueidentifier  NULL,
    [StatusId] uniqueidentifier  NULL,
    [HeadImageId] uniqueidentifier  NULL
);
GO

-- Creating table 'UserHeadImageSet'
CREATE TABLE [dbo].[UserHeadImageSet] (
    [Id] uniqueidentifier  NOT NULL,
    [ImageData] varbinary(max)  NOT NULL,
    [ImageType] nvarchar(max)  NOT NULL,
    [UpdateOn] datetime  NOT NULL
);
GO

-- Creating table 'PermissionSet'
CREATE TABLE [dbo].[PermissionSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [DefaultDeny] bit  NOT NULL
);
GO

-- Creating table 'Permission_GroupSet'
CREATE TABLE [dbo].[Permission_GroupSet] (
    [PermissionId] uniqueidentifier  NOT NULL,
    [GroupId] uniqueidentifier  NOT NULL,
    [HasPermission] bit  NOT NULL
);
GO

-- Creating table 'OrganizationSet'
CREATE TABLE [dbo].[OrganizationSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Icon] nvarchar(max)  NOT NULL,
    [UpdateOn] datetime  NOT NULL
);
GO

-- Creating table 'AppFunctionSet'
CREATE TABLE [dbo].[AppFunctionSet] (
    [Id] uniqueidentifier  NOT NULL,
    [AppName] nvarchar(max)  NOT NULL,
    [FunctionTypeId] uniqueidentifier  NOT NULL,
    [FunctionCategoryId] uniqueidentifier  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [IconName] nvarchar(max)  NOT NULL,
    [FuncUrl] nvarchar(max)  NOT NULL,
    [Privilege] int  NOT NULL,
    [UserAgent] nvarchar(max)  NOT NULL,
    [PermissionNeed] nvarchar(max)  NULL
);
GO

-- Creating table 'AppFunctionTypeSet'
CREATE TABLE [dbo].[AppFunctionTypeSet] (
    [Id] uniqueidentifier  NOT NULL,
    [TypeName] nvarchar(max)  NOT NULL,
    [DisplayText] nvarchar(max)  NOT NULL,
    [IconName] nvarchar(max)  NOT NULL,
    [Privilege] int  NOT NULL
);
GO

-- Creating table 'AppFunctionShortcutSet'
CREATE TABLE [dbo].[AppFunctionShortcutSet] (
    [Id] uniqueidentifier  NOT NULL,
    [UserId] uniqueidentifier  NOT NULL,
    [ShortcutSequence] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'FeedSet'
CREATE TABLE [dbo].[FeedSet] (
    [Id] uniqueidentifier  NOT NULL,
    [TemplateId] uniqueidentifier  NOT NULL,
    [FeedData] nvarchar(max)  NOT NULL,
    [OwnerId] uniqueidentifier  NULL,
    [CreateOn] datetime  NOT NULL,
    [ExpireOn] datetime  NOT NULL,
    [Icon] nvarchar(max)  NOT NULL,
    [UpdateOn] datetime  NOT NULL
);
GO

-- Creating table 'Feed_RecipientSet'
CREATE TABLE [dbo].[Feed_RecipientSet] (
    [Id] uniqueidentifier  NOT NULL,
    [FeedId] uniqueidentifier  NOT NULL,
    [FeedTypeValue] int  NOT NULL,
    [FeedDataEx] nvarchar(max)  NULL,
    [IsRead] bit  NOT NULL,
    [IsAvailable] bit  NOT NULL,
    [IsMark] bit  NOT NULL,
    [LastChangeOn] datetime  NOT NULL,
    [RecipientId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Feed_RecordSet'
CREATE TABLE [dbo].[Feed_RecordSet] (
    [Identity] uniqueidentifier  NOT NULL,
    [TemplateName] nchar(50)  NOT NULL,
    [UserId] uniqueidentifier  NOT NULL,
    [FeedId] uniqueidentifier  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'FeedTemplateSet'
ALTER TABLE [dbo].[FeedTemplateSet]
ADD CONSTRAINT [PK_FeedTemplateSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'DeptSet'
ALTER TABLE [dbo].[DeptSet]
ADD CONSTRAINT [PK_DeptSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'GroupSet'
ALTER TABLE [dbo].[GroupSet]
ADD CONSTRAINT [PK_GroupSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PositionSet'
ALTER TABLE [dbo].[PositionSet]
ADD CONSTRAINT [PK_PositionSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'UserProfileSet'
ALTER TABLE [dbo].[UserProfileSet]
ADD CONSTRAINT [PK_UserProfileSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'UserHeadImageSet'
ALTER TABLE [dbo].[UserHeadImageSet]
ADD CONSTRAINT [PK_UserHeadImageSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PermissionSet'
ALTER TABLE [dbo].[PermissionSet]
ADD CONSTRAINT [PK_PermissionSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [PermissionId], [GroupId] in table 'Permission_GroupSet'
ALTER TABLE [dbo].[Permission_GroupSet]
ADD CONSTRAINT [PK_Permission_GroupSet]
    PRIMARY KEY CLUSTERED ([PermissionId], [GroupId] ASC);
GO

-- Creating primary key on [Id] in table 'OrganizationSet'
ALTER TABLE [dbo].[OrganizationSet]
ADD CONSTRAINT [PK_OrganizationSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'AppFunctionSet'
ALTER TABLE [dbo].[AppFunctionSet]
ADD CONSTRAINT [PK_AppFunctionSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'AppFunctionTypeSet'
ALTER TABLE [dbo].[AppFunctionTypeSet]
ADD CONSTRAINT [PK_AppFunctionTypeSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'AppFunctionShortcutSet'
ALTER TABLE [dbo].[AppFunctionShortcutSet]
ADD CONSTRAINT [PK_AppFunctionShortcutSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'FeedSet'
ALTER TABLE [dbo].[FeedSet]
ADD CONSTRAINT [PK_FeedSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Feed_RecipientSet'
ALTER TABLE [dbo].[Feed_RecipientSet]
ADD CONSTRAINT [PK_Feed_RecipientSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Identity], [UserId], [TemplateName] in table 'Feed_RecordSet'
ALTER TABLE [dbo].[Feed_RecordSet]
ADD CONSTRAINT [PK_Feed_RecordSet]
    PRIMARY KEY CLUSTERED ([Identity], [UserId], [TemplateName] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [GroupId] in table 'DeptSet'
ALTER TABLE [dbo].[DeptSet]
ADD CONSTRAINT [FK_GroupDept]
    FOREIGN KEY ([GroupId])
    REFERENCES [dbo].[GroupSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GroupDept'
CREATE INDEX [IX_FK_GroupDept]
ON [dbo].[DeptSet]
    ([GroupId]);
GO

-- Creating foreign key on [PositionId] in table 'UserProfileSet'
ALTER TABLE [dbo].[UserProfileSet]
ADD CONSTRAINT [FK_PosotionUserProfile]
    FOREIGN KEY ([PositionId])
    REFERENCES [dbo].[PositionSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PosotionUserProfile'
CREATE INDEX [IX_FK_PosotionUserProfile]
ON [dbo].[UserProfileSet]
    ([PositionId]);
GO

-- Creating foreign key on [DeptId] in table 'UserProfileSet'
ALTER TABLE [dbo].[UserProfileSet]
ADD CONSTRAINT [FK_DeptUserProfile]
    FOREIGN KEY ([DeptId])
    REFERENCES [dbo].[DeptSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DeptUserProfile'
CREATE INDEX [IX_FK_DeptUserProfile]
ON [dbo].[UserProfileSet]
    ([DeptId]);
GO

-- Creating foreign key on [HeadImageId] in table 'UserProfileSet'
ALTER TABLE [dbo].[UserProfileSet]
ADD CONSTRAINT [FK_UserHeadImageUserProfile]
    FOREIGN KEY ([HeadImageId])
    REFERENCES [dbo].[UserHeadImageSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserHeadImageUserProfile'
CREATE INDEX [IX_FK_UserHeadImageUserProfile]
ON [dbo].[UserProfileSet]
    ([HeadImageId]);
GO

-- Creating foreign key on [GroupId] in table 'Permission_GroupSet'
ALTER TABLE [dbo].[Permission_GroupSet]
ADD CONSTRAINT [FK_GroupPermissionGroup]
    FOREIGN KEY ([GroupId])
    REFERENCES [dbo].[GroupSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GroupPermissionGroup'
CREATE INDEX [IX_FK_GroupPermissionGroup]
ON [dbo].[Permission_GroupSet]
    ([GroupId]);
GO

-- Creating foreign key on [PermissionId] in table 'Permission_GroupSet'
ALTER TABLE [dbo].[Permission_GroupSet]
ADD CONSTRAINT [FK_PermissionPermissionGroup]
    FOREIGN KEY ([PermissionId])
    REFERENCES [dbo].[PermissionSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [FunctionCategoryId] in table 'AppFunctionSet'
ALTER TABLE [dbo].[AppFunctionSet]
ADD CONSTRAINT [FK_AppFunctionCategoryAppFunction]
    FOREIGN KEY ([FunctionCategoryId])
    REFERENCES [dbo].[AppFunctionTypeSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AppFunctionCategoryAppFunction'
CREATE INDEX [IX_FK_AppFunctionCategoryAppFunction]
ON [dbo].[AppFunctionSet]
    ([FunctionCategoryId]);
GO

-- Creating foreign key on [FunctionTypeId] in table 'AppFunctionSet'
ALTER TABLE [dbo].[AppFunctionSet]
ADD CONSTRAINT [FK_AppFunctionTypeAppFunction]
    FOREIGN KEY ([FunctionTypeId])
    REFERENCES [dbo].[AppFunctionTypeSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AppFunctionTypeAppFunction'
CREATE INDEX [IX_FK_AppFunctionTypeAppFunction]
ON [dbo].[AppFunctionSet]
    ([FunctionTypeId]);
GO

-- Creating foreign key on [UserId] in table 'AppFunctionShortcutSet'
ALTER TABLE [dbo].[AppFunctionShortcutSet]
ADD CONSTRAINT [FK_UserProfileAppFunctionShortcut]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[UserProfileSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserProfileAppFunctionShortcut'
CREATE INDEX [IX_FK_UserProfileAppFunctionShortcut]
ON [dbo].[AppFunctionShortcutSet]
    ([UserId]);
GO

-- Creating foreign key on [FeedId] in table 'Feed_RecordSet'
ALTER TABLE [dbo].[Feed_RecordSet]
ADD CONSTRAINT [FK_FeedFeed_Record]
    FOREIGN KEY ([FeedId])
    REFERENCES [dbo].[FeedSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FeedFeed_Record'
CREATE INDEX [IX_FK_FeedFeed_Record]
ON [dbo].[Feed_RecordSet]
    ([FeedId]);
GO

-- Creating foreign key on [FeedId] in table 'Feed_RecipientSet'
ALTER TABLE [dbo].[Feed_RecipientSet]
ADD CONSTRAINT [FK_FeedFeed_Recipient]
    FOREIGN KEY ([FeedId])
    REFERENCES [dbo].[FeedSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FeedFeed_Recipient'
CREATE INDEX [IX_FK_FeedFeed_Recipient]
ON [dbo].[Feed_RecipientSet]
    ([FeedId]);
GO

-- Creating foreign key on [TemplateId] in table 'FeedSet'
ALTER TABLE [dbo].[FeedSet]
ADD CONSTRAINT [FK_FeedTemplateFeed]
    FOREIGN KEY ([TemplateId])
    REFERENCES [dbo].[FeedTemplateSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FeedTemplateFeed'
CREATE INDEX [IX_FK_FeedTemplateFeed]
ON [dbo].[FeedSet]
    ([TemplateId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------